CREATE VIEW dbo.vDistributionReport
AS
SELECT DISTINCT
                      T.BT_ID AS ID, I.ORIGINATING_TRANS_NUM AS OriginalTransaction, T.TRANS_NUMBER AS TransactionNumber,
                      T.TRANSACTION_DATE AS TransactionDate, I.INSTALL_BILL_DATE AS InstallDate, T.ACTIVITY_TYPE AS GiftType, CONVERT(float, - T.AMOUNT)
                      AS Amount, A.CAMPAIGN_CODE AS Campaign, T.OWNER_ORG_CODE AS Fund, A.SOURCE_CODE AS Appeal, T.PRODUCT_CODE AS Distribution,
                      T.SOLICITOR_ID AS SolicitorID, CONVERT(int, SUBSTRING(CONVERT(char(6), T.FISCAL_PERIOD), 5, 2)) AS FiscalMonth, CONVERT(int,
                      SUBSTRING(CONVERT(char(6), T.FISCAL_PERIOD), 1, 4)) AS FiscalYear, I.SOURCE_SYSTEM AS SourceSystem
FROM         dbo.Trans AS T LEFT OUTER JOIN
                      dbo.Activity AS A ON T.ACTIVITY_SEQN = A.SEQN LEFT OUTER JOIN
                      dbo.Invoice AS I ON I.ORIGINATING_TRANS_NUM = T.TRANS_NUMBER
WHERE     (T.JOURNAL_TYPE = 'IN') AND (T.TRANSACTION_TYPE = 'DIST') AND (T.SOURCE_SYSTEM = 'FR')

GO
GRANT REFERENCES ON  [dbo].[vDistributionReport] TO [IMIS]
GRANT SELECT ON  [dbo].[vDistributionReport] TO [IMIS]
GRANT INSERT ON  [dbo].[vDistributionReport] TO [IMIS]
GRANT DELETE ON  [dbo].[vDistributionReport] TO [IMIS]
GRANT UPDATE ON  [dbo].[vDistributionReport] TO [IMIS]
GO